# sensor_processor.py

import pandas as pd
from sqlalchemy import create_engine, text
from datetime import datetime, timedelta

# Configuration
DB_URL = "your_connection_string_here"  # Change this
REFERENCE_CSV = "refgas.csv"            # Renamed from csv.txt

engine = create_engine(DB_URL)

def process_sensor_data(site_id: int, begin_date: str):
    """
    Process and correct sensor data for a specific site and date range.
    
    Parameters:
        site_id (int): 1, 2, or 3 corresponding to table1, table2, or table3.
        begin_date (str): 'YYYY-MM-DD' format. Up to 30 days will be processed.
    """
    if site_id not in [1, 2, 3]:
        raise ValueError("Invalid site_id. Must be 1, 2, or 3.")

    table_name = f"table{site_id}"
    begin_dt = datetime.strptime(begin_date, "%Y-%m-%d")
    end_dt = begin_dt + timedelta(days=30)

    # Load reference values from refgas.csv
    ref_df = pd.read_csv(REFERENCE_CSV, parse_dates=['from_date'])

    def get_ref_value(stype: str):
        matched = ref_df[
            (ref_df['site_id'] == site_id) &
            (ref_df['from_date'] == begin_dt) &
            (ref_df['stype'].str.upper() == stype.upper())
        ]
        if matched.empty:
            raise ValueError(f"❌ No reference value found for site {site_id}, date {begin_date}, type {stype}")
        return float(matched.iloc[0]['value'])

    reference_a = get_ref_value('W')
    reference_t = get_ref_value('T')

    # Check and prompt to delete existing adjusted_data records
    check_query = """
    SELECT COUNT(*) AS count FROM adjusted_data
    WHERE site_id = :site_id AND timestamp >= :begin_dt AND timestamp < :end_dt
    """
    with engine.connect() as conn:
        result = conn.execute(text(check_query), {
            "site_id": site_id,
            "begin_dt": begin_dt,
            "end_dt": end_dt
        }).fetchone()
        existing_count = result['count']

    if existing_count > 0:
        print(f"⚠️ Found {existing_count} existing records in adjusted_data for site {site_id} between {begin_date} and {end_dt.date()}.")
        confirm = input("❓ Do you want to delete them and continue? (yes/no): ").strip().lower()
        if confirm != 'yes':
            print("🚫 Aborted by user.")
            return
        # Delete
        with engine.begin() as conn:
            conn.execute(text("""
                DELETE FROM adjusted_data
                WHERE site_id = :site_id AND timestamp >= :begin_dt AND timestamp < :end_dt
            """), {"site_id": site_id, "begin_dt": begin_dt, "end_dt": end_dt})
        print("🗑️ Existing records deleted.")

    # Load raw sensor data
    query = f"""
    SELECT timestamp, value, type FROM {table_name}
    WHERE timestamp >= :begin_dt AND timestamp < :end_dt
    ORDER BY timestamp
    """
    df = pd.read_sql_query(text(query), engine, params={"begin_dt": begin_dt, "end_dt": end_dt})

    if df.empty:
        print(f"📭 No data found in {table_name} from {begin_date} to {end_dt.date()}")
        return

    df['adjusted_value'] = None
    w_index = None
    next_w_index = None
    i = 0
    n = len(df)

    while i < n:
        if df.loc[i, 'type'] == 'w':
            if w_index is None:
                w_index = i
            else:
                next_w_index = i
                avg_w = (df.loc[w_index, 'value'] + df.loc[next_w_index, 'value']) / 2.0

                for j in range(w_index + 1, next_w_index):
                    row_type = df.loc[j, 'type']
                    x = df.loc[j, 'value']
                    if row_type == 'a':
                        df.at[j, 'adjusted_value'] = x * reference_a / avg_w
                    elif row_type == 't':
                        df.at[j, 'adjusted_value'] = x * reference_t / avg_w

                w_index = next_w_index
                next_w_index = None
        i += 1

    df['site_id'] = site_id

    # Ensure adjusted_data table exists
    create_stmt = """
    CREATE TABLE IF NOT EXISTS adjusted_data (
        timestamp DATETIME,
        value FLOAT,
        type CHAR(1),
        adjusted_value FLOAT,
        site_id INT
    )
    """
    with engine.begin() as conn:
        conn.execute(text(create_stmt))

    # Save
    df.to_sql("adjusted_data", con=engine, if_exists="append", index=False)
    print(f"✅ Processed and saved {len(df)} rows for site {site_id} from {begin_date} to {end_dt.date()}")
